﻿/// <summary>
/// Create By:  Alex Sanchez Sanchez
/// Date:       20/11/2012
/// Time:       08:10
/// Update:     
/// Time Update: 
/// Fix Bugs Descripion:
/// 
/// </summary>

using System;
using System.Linq;
using Npgsql;
using Shared;

namespace LayerData.DataBaseManager
{
    /// <summary>
    /// Clase que administra las propiedades y metodos basicos de una conexion al servidor.
    /// </summary>
    internal class PostgreSqlConnectionInfo : IPostgreSqlConnectionInfo 
    {
        #region Construtor
        /// <summary>
        /// Inicializa la clase <see cref="PostgreSqlConnectionInfo"/>
        /// </summary>
        /// <param name="serverName">Nombre del Servidor de datos.</param>
        /// <param name="portNumber">Numero del Puerto.</param>
        /// <param name="dataBaseName">Nombre de la Base de Datos.</param>
        /// <param name="userName">Nombre del Usuario</param>
        /// <param name="password">Password del Usuario a Utilizar</param>
        /// <param name="itengratedSecurity">Seguridad Integrada de Windows</param>
        public PostgreSqlConnectionInfo(string serverName, int portNumber, string dataBaseName, string userName, string password, bool itengratedSecurity)
        {
            this.strHost = serverName;
            this.intPort = portNumber;
            this.strDataBase = dataBaseName;
            this.strUserName = userName;
            this.strPassword = password;
            this.bIntegratedSecurity = itengratedSecurity;

            string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", this.strHost, this.intPort, this.strUserName, this.strPassword, this.strDataBase);
            this.conBuild = new NpgsqlConnectionStringBuilder(connstring);
        }

        /// <summary>
        /// Inicializa la clase <see cref="PostgreSqlConnectionInfo"/> con los valores por default
        /// </summary>
        public PostgreSqlConnectionInfo()
        {
            this.strHost = "localhost";
            this.intPort = 5432;
            this.strDataBase = "DB_System2012";
            this.strUserName = "postgres";
            this.strPassword = "postgres";
            this.bIntegratedSecurity = false ;
            string connstring = String.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", this.strHost, this.intPort, this.strUserName, this.strPassword, this.strDataBase);
            this.conBuild = new NpgsqlConnectionStringBuilder(connstring);
        }

        #endregion

        #region Variables

        readonly NpgsqlConnectionStringBuilder conBuild = new NpgsqlConnectionStringBuilder();

        private string strHost;

        private int intPort;

        private string strDataBase;

        private string strUserName;

        private string strPassword;

        private bool bUseSsl;

        private int intTimeOut;

        private int intConnectionLifeTime;

        private int intMaxPoolSize;

        private int intCommandTimeout;

        private bool bIntegratedSecurity;

        private bool bUsePooling;

        #endregion

        #region Propiedades

         private Boolean transaccion;
         public Boolean Transaccion
         {
             get
             {
                 return this.transaccion;
             }
             set
             {
                 this.transaccion = value;
             }
         }
        private NpgsqlTransaction tsql;
        public NpgsqlTransaction Transacc
        {
            get
            {
                return tsql;
            }
            set {
                tsql = value;
            }
        }

        /// <summary>
        /// Nombre del Servidor(default: localhost)
        /// </summary>
        public string ServerName
        {
            get
            {
                return this.strHost;
            }
            set
            {
                this.strHost = value;
                this.conBuild.Host = value;
            }
        }

        /// <summary>
        /// Numero de Puerto (default : 5432)
        /// </summary>
        public int PortNumber
        {
            get
            {
                return this.intPort;
            }
            set
            {
                this.intPort = value;
                this.conBuild.Port = value;
            }
        }

        /// <summary>
        /// Obtiene el nombre de la base de datos actual o de la base de datos para ser utilizado 
        /// después de la conexión se abre.
        /// </summary>
        public string DataBaseName
        {
            get
            {
                return this.strDataBase;
            }

            set
            {
                this.strDataBase = value;
                this.conBuild.Database = value;
            }
        }
        
        /// <summary>
        /// Passowrd del Usuario (Default: postgres)
        /// </summary>
        public string Password
        {
            get
            {
                return this.strPassword;
            }
            set
            {
                this.strPassword = value;
                this.conBuild.Password = value;
            }
        }

        public bool Pooling
        {
            get
            {
                return this.bUsePooling;
            }
            set
            {
                this.bUsePooling = value;
                this.conBuild.Pooling = value;
            }
        }

        /// <summary>
        /// Inidica si se usara seguridad integrada de windows( default: False)
        /// </summary>
        public bool ItengratedSecurity
        {
            get
            {
                return this.bIntegratedSecurity;
            }
            set
            {
                this.bIntegratedSecurity = value;
                this.conBuild.IntegratedSecurity = value;
            }
        }

        /// <summary>
        /// Indica el numero maximo de conexion
        /// </summary>
        public int MaxPoolSize
        {
            get
            {
                return this.intMaxPoolSize;
            }
            set
            {
                this.intMaxPoolSize = value;
                this.conBuild.MaxPoolSize = value;
            }
        }

        /// <summary>
        /// Obtiene o establece si se usara Seguridad SSL
        /// </summary>
        public bool Ssl
        {
            get
            {
                return this.bUseSsl;
            }
            set
            {
                this.bUseSsl = value;
                this.conBuild.SSL = value;
            }
        }

        /// <summary>
        /// Obtiene o establece el tiempo de espera para intentar establecer una conexión 
        /// antes de terminar el intento y generar un error.
        /// </summary>
        public int ConnectionTimeout
        {
            get
            {
                return this.intTimeOut;
            }
            set
            {
                this.intTimeOut = value;
                this.conBuild.Timeout = value;
            }
        }

        /// <summary>
        /// Nombre del Usario(Defualt: postgres)
        /// </summary>
        public string UserName
        {
            get
            {
                if (this.ItengratedSecurity)
                {
                    this.strUserName = Utils.GetCurrentUserName();
                }
                return this.strUserName;
            }
            set
            {
                this.strUserName = value;
                this.conBuild.UserName = value;
            }
        }

        /// <summary>
        /// Obtiene el tiempo de espera al intentar ejecutar un comando antes de terminar el intento y generar un error.
        /// </summary>
        public int CommandTimeout
        {
            get
            {
                return this.intCommandTimeout;
            }
            set
            {
                this.intCommandTimeout = value;

                this.conBuild.CommandTimeout = value;
            }
        }

        /// <summary>
        /// Obtiene el tiempo de espera antes de cerrar conexiones no utilizadas en el grupo de conexion, 
        /// si el recuento de todas las conexiones supera MinPoolSize.
        /// </summary>
        public int ConnectionLifeTime
        {
            get
            {
                return this.intConnectionLifeTime;
            }
            set
            {
                this.intConnectionLifeTime = value;
                this.conBuild.ConnectionLifeTime = value;
            }
        }
        #endregion

        #region Funciones
        /// <summary>
        /// Devuelve el Connection String para conectarse al Servidor.
        /// </summary>
        /// <returns></returns>
        public string GetStringPostgreSqlConnection()
        {
            return this.conBuild.ConnectionString;
        }

        /// <summary>
        /// Devuelve un Nuevo NpgsqlConnection
        /// </summary>
        /// <returns></returns>
        public NpgsqlConnection GetPostgreSqlConnection()
        {
            return new NpgsqlConnection(this.conBuild.ConnectionString);
        }

        /// <summary>
        /// Crea una Instancia de NpgsqlTransaction y abre la conexion a la Base de Datos.
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        public NpgsqlTransaction GetTransaction(NpgsqlConnection con) {
            NpgsqlTransaction tran = null;
            if (con != null) {
                OpenConnection(con);
                tran = con.BeginTransaction();
            }
            return tran;
        }

        #endregion

        #region Metodos

        /// <summary>
        /// Cierra una conexion 
        /// </summary>
        /// <param name="con">Conexion a cerrar</param>
        public void CloseConnection(NpgsqlConnection con)
        {
            if (con != null)
            {
                transaccion = false;
                if (con.State == System.Data.ConnectionState.Open)
                {
                    con.Close();
                    //con.Dispose();
                    Console.Write("Conexion cerrada" + Environment .NewLine );
                }
            }
        }

        /// <summary>
        /// Abre una conexion
        /// </summary>
        /// <param name="con">Conexion a abrir.</param>
        public void OpenConnection(NpgsqlConnection con)
        {
            if (con != null)
            {
                 transaccion = false;
                if (con.State == System.Data.ConnectionState.Closed)
                {
                    try
                    {
                        con.Open();
                    }
                    catch (NpgsqlException ex)
                    {
                        Console.Write(ex.Message + Environment.NewLine );
                        throw new Exception(ex.Message, null);
                    }
                }
            }
        }

        /// <summary>
        /// Comit a una NpgsqlTransaction
        /// </summary>
        /// <param name="tran"></param>
        public void CommintTransaction(NpgsqlTransaction tran) {
            if (tran != null) {
                tran.Commit();
                CloseConnection(tran.Connection);
            }
        }


        public void AbrirConexionTrans(NpgsqlConnection con)
        {
            try
            {
                if (transaccion != true)
                {
                    this.OpenConnection(con);
                    tsql = con.BeginTransaction();
                    transaccion = true;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message + Environment.NewLine );
            }
        }



        public void CerrarConexionTrans(NpgsqlConnection con)
        {
            try
            {
                if (transaccion == true)
                {
                    tsql.Commit();
                    CloseConnection(con);
                    transaccion = false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
        }
        public void CancelarConexionTrans(NpgsqlConnection con)
        {
            try
            {
                if (transaccion == true)
                {
                    tsql.Rollback();
                    CloseConnection(con);
                    transaccion = false;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
            }
        }
        #endregion
    }
}